{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Congestion Charges - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "List the owners (name and address) of Vehicles caught by camera 1 or 18 without duplication."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ambiguous, Arthur</td>\n",
       "        <td>Absorption Ave.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Inconspicuous, Iain</td>\n",
       "        <td>Interception Rd.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Strenuous, Sam</td>\n",
       "        <td>Surjection Street</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ambiguous, Arthur', 'Absorption Ave.'),\n",
       " ('Inconspicuous, Iain', 'Interception Rd.'),\n",
       " ('Strenuous, Sam', 'Surjection Street')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT name, address\n",
    "  FROM keeper JOIN vehicle ON (keeper.id=vehicle.keeper) JOIN\n",
    "    image ON (image.reg=vehicle.id)\n",
    "    WHERE camera IN (1, 18);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Show keepers (name and address) who have more than 5 vehicles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ambiguous, Arthur</td>\n",
       "        <td>Absorption Ave.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Inconspicuous, Iain</td>\n",
       "        <td>Interception Rd.</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Ambiguous, Arthur', 'Absorption Ave.'),\n",
       " ('Inconspicuous, Iain', 'Interception Rd.')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT name, address\n",
    "  FROM keeper JOIN vehicle ON (keeper.id=vehicle.keeper)\n",
    "    GROUP BY keeper.id\n",
    "    HAVING COUNT(*)>5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "For each vehicle show the number of current permits (suppose today is the 1st of Feb 2007). The list should include the vehicle.s registration and the number of permits. Current permits can be determined based on charge types, e.g. for weekly permit you can use the date after 24 Jan 2007 and before 02 Feb 2007."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>reg</th>\n",
       "        <th>n_permit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 DSP</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 DTP</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 JSP</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 KSP</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 KTP</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 QSP</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 RSP</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SO 02 DSP', 1),\n",
       " ('SO 02 DTP', 1),\n",
       " ('SO 02 JSP', 1),\n",
       " ('SO 02 KSP', 1),\n",
       " ('SO 02 KTP', 1),\n",
       " ('SO 02 QSP', 1),\n",
       " ('SO 02 RSP', 1)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  SELECT reg, sdate, \n",
    "    CASE WHEN chargetype='Daily' THEN sdate+INTERVAL '1d'\n",
    "        WHEN chargetype='Weekly' THEN sdate+INTERVAL '1 week'\n",
    "        WHEN chargetype='Monthly' THEN sdate+INTERVAL '1 month'\n",
    "        WHEN chargetype='Annual' THEN sdate+INTERVAL '1 year'\n",
    "      END AS edate\n",
    "      FROM permit\n",
    ")\n",
    "SELECT reg, COUNT(*) n_permit\n",
    "  FROM t\n",
    "    WHERE '2007-2-1'::date BETWEEN sdate AND edate\n",
    "    GROUP BY reg\n",
    "    ORDER BY reg;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "Obtain a list of every vehicle passing camera 10 on 25th Feb 2007. Show the time, the registration and the name of the keeper if available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>reg</th>\n",
       "        <th>whn</th>\n",
       "        <th>keeper</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 CSP</td>\n",
       "        <td>2007-02-25 07:45:11</td>\n",
       "        <td>Ambiguous, Arthur</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SO 02 ESP</td>\n",
       "        <td>2007-02-25 18:08:40</td>\n",
       "        <td>Ambiguous, Arthur</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SO 02 CSP', datetime.datetime(2007, 2, 25, 7, 45, 11), 'Ambiguous, Arthur'),\n",
       " ('SO 02 ESP', datetime.datetime(2007, 2, 25, 18, 8, 40), 'Ambiguous, Arthur')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT reg, whn, name keeper\n",
    "  FROM vehicle JOIN keeper ON (vehicle.keeper=keeper.id) JOIN\n",
    "    image ON (vehicle.id=image.reg)\n",
    "    WHERE whn::DATE='2007-2-25'::DATE AND camera=10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "List the keepers who have more than 4 vehicles and one of them must have more than 2 permits. The list should include the names and the number of vehicles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>n_vehicle</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Inconspicuous, Iain</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Inconspicuous, Iain', 7)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    -- keepers who have more than 2 permits\n",
    "    SELECT keeper FROM vehicle\n",
    "      WHERE id IN (\n",
    "        SELECT reg FROM permit\n",
    "          GROUP BY reg\n",
    "          HAVING COUNT(*)>2)\n",
    ")\n",
    "SELECT keeper.name, COUNT(*) n_vehicle\n",
    "  FROM vehicle JOIN keeper ON (vehicle.keeper=keeper.id)\n",
    "    WHERE keeper.id IN (\n",
    "        SELECT keeper FROM t)\n",
    "    GROUP BY keeper.name\n",
    "    HAVING COUNT(*)>4;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
